<?php
/**
 * 城市数据采集
 * User: Administrator
 * Date: 2015/12/11
 * Time: 20:21
 */
class DwDataCityCommand extends ETLCommand{
    /**
     * 查询数据
     */
    public function actionRun(){
        phpQuery::newDocumentFile('http://www.58.com/changecity.aspx?PGTID=0d100000-0000-3873-77e4-e2f6fc5ba527&ClickID=1');
        $cityList = pq("#clist dd a");
        $pinyin  = new Pinyin();

        foreach($cityList as $city ){
            $insertData = array();
            $insertData['city_name'] = pq($city)->html();
            $sql = "select id from dw_data_city where city_name='{$insertData['city_name']}'";
            $isExsit = Yii::app()->db->createCommand($sql)->queryRow();
            if($isExsit) continue;
            $insertData['city_pinyin'] = $pinyin->Input($insertData['city_name']);
            $insertData['job_num'] = $this->_getCityTotalJonNum($insertData['city_name']);
            //判断职位需求量是否大于5000
            if($insertData['job_num']>5000){
                $insertData['is_statistics'] = 1;
            }
            $this->actionInsert('dw_data_city', $insertData);
        }
        echo 'end';

    }

    public function _getCityTotalJonNum($cityName){
        $str = "http://sou.zhaopin.com/jobs/searchresult.ashx?jl={$cityName}&sm=0&p=1";
        phpQuery::newDocumentFile($str);
        $jobNum = pq(".seach_yx span.search_yx_tj em")->html();
        return $jobNum;
    }

    /**
     * 插入数据
     */
    public function actionInsert($table, $row){
        try{
            Yii::app()->db->createCommand()->insert($table, $row);
        }catch(Excwption $ex){
            trigger_error($ex->getMessage(),E_USER_ERROR);
        }

    }

    /**
     * 创建表
     */
    public function actionCreateTabel(){
        $sql = "
            CREATE TABLE `dw_data_city` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
          `city_name` varchar(128) NOT NULL COMMENT '城市名',
          `city_pinyin` varchar(128) NOT NULL COMMENT '城市名对应的拼音',
          `job_num` int(11) NOT NULL COMMENT '招聘总量',
          `is_statistics` int(11) NOT NULL DEFAULT '0' COMMENT '是否统计',
          `comments` varchar(255) DEFAULT NULL COMMENT '备注',
          PRIMARY KEY (`id`),
          UNIQUE KEY `city_name` (`city_name`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据仓库-城市名'
        ";
        Yii::app()->db->createCommand($sql)->execute();
        echo 'create table successfully';
    }
}